# -*- coding:utf8 -*-
import csv
#des模式  填充方式  CBC加密方式
from pyDes import des, PAD_PKCS5, CBC
import base64
import pymysql
import binascii
import sys
import os

def des_encrypt(s):
    """
    DES 加密
    :param s: 原始字符串
    :return: 加密后字符串，16进制
    """
    secret_key = 'python11'
    iv = "12345678"
    k = des(secret_key, CBC, iv, pad=None, padmode=PAD_PKCS5)
    en = k.encrypt(s.encode(), padmode=PAD_PKCS5)
    return base64.b64encode(en).decode()

def des_decrypt(d, k=None):
    """
    DES 解密
    :param d: 加密字符串
    :param k: des
    :return: 解密后字符串
    """
    secret_key = 'python11'
    iv = "12345678"
    if k==None :
        k = des(secret_key, CBC, iv, pad=None, padmode=PAD_PKCS5)
    d = base64.b64decode(d)
    de = k.decrypt(d, padmode=PAD_PKCS5)
    return bytes.decode(de)


#多行插入
def insert_many(connect, cursor, insert_sql, insert_data):
    connect.ping(reconnect = True)
    res = 0
    # 批量插入
    try:
        res = cursor.executemany(insert_sql, insert_data)
        print("res = ", res)
        # 提交，不进行提交无法保存到数据库
        connect.commit()
    except Exception as e:
        print(e)
        connect.rollback()
    #finally:
    #connect.close()

    return res

def do_import(data_file):
    mysql_conn=pymysql.connect(host = '172.28.56.77', user = 'mysql', passwd = 'qwert12345', db = 'origin_data')
    cursor=mysql_conn.cursor()

    secret_key = 'python11'
    iv = "12345678"
    k = des(secret_key, CBC, iv, pad=None, padmode=PAD_PKCS5)

    insert_sql = """
            insert into imei_statis_base_202012(
                imei, des_imei, city_name, date_day
                ) VALUES (%s, %s, %s, %s)
            """
    with open(data_file, 'r', encoding='utf-8') as f:
        #reader = csv.reader(f)
        rows = f.readlines()
        #跳过第一行表头
        header = rows.pop(0)
        print(header)

        maxRow = len(rows)
        print("总记录数", maxRow)

        index = 0
        insert_data = []
        for row in rows:
            if not row:
                continue
            #print(row)
            cols = row.strip().split('\t')
            imei = des_decrypt(cols[1], k)
            #imei, des_imei, city_name, date_day
            rowData = (imei, cols[1], cols[0], cols[2])
            insert_data.append(rowData)
            index = index + 1
            if len(insert_data) > 0 and (index % 1000 == 0 or index == maxRow):
                #print(index)
                # 批量写入1000条数据
                insert_many(mysql_conn, cursor, insert_sql, insert_data)
                insert_data.clear()
            #break

def write_log(log_file, cur_date):
    with open(log_file, 'w') as f:
        f.write(cur_date)

if __name__ == '__main__':

    e = des_encrypt('35329607005281')
    print(e)
    d = des_decrypt(e)
    print(d)

    try:
        fc_file = 'D:\\data\\csv\\month12_detail.csv'
        #fc_file = fc_file.decode('utf8').encode('gbk')
        if not os.path.exists(fc_file):
            print('err:file not exist:' + fc_file)
            exit()
        do_import(fc_file)
    except Exception as ex:
        print ('ex:', ex)
